CREATE OR REPLACE FUNCTION evaluate_Referred_and_Seriousness_ticket_features2() RETURNS VOID AS '
DECLARE
 

v_sSMHTicketsMatched INTEGER;
  v_sSMHTicketsFound INTEGER;
  v_sSMHTicketsTheir INTEGER;
  v_sSMHRecall REAL;
  v_sSMHPrecision REAL;

  v_MHETicketsMatched INTEGER;
  v_MHETicketsFound INTEGER;
  v_MHETicketsTheir INTEGER;
  v_MHERecall REAL;
  v_MHEPrecision REAL;


v_ShortMHETicketsMatched INTEGER;
  v_ShortMHETicketsFound INTEGER;
  v_ShortMHETicketsTheir INTEGER;
  v_ShortMHERecall REAL;
  v_ShortMHEPrecision REAL;


 
BEGIN

--Query
--Short SMH
 select count(*) INTO v_sSMHTicketsMatched
from ticketfeatures t, "small_smh_ticket_fire_blow_smok" s  
where t.troubletype  in (''SMH'') and seriousmetadata is not null
 and s.ticket = t.ticketnumber and t.seriousmetadata=1 and s.ser1_notser0=1;

 select count(*) INTO v_sSMHTicketsFound
from ticketfeatures t, "small_smh_ticket_fire_blow_smok" s  
where t.troubletype  in (''SMH'') and seriousmetadata is not null
 and s.ticket = t.ticketnumber and t.seriousmetadata=1;

 select count(*) INTO v_sSMHTicketsTheir
from ticketfeatures t, "small_smh_ticket_fire_blow_smok" s  
where t.troubletype  in (''SMH'') and seriousmetadata is not null
 and s.ticket = t.ticketnumber and  s.ser1_notser0=1;


--MHE Tickets
 select count(*) INTO v_MHETicketsMatched
from ticketfeatures t, "mhevent_metadata2" s  
where t.troubletype  in (''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null 
and s.ticket = t.ticketnumber and t.seriousmetadata=1 and s.short_mhevent_ser1notser0=1;

select count(*) INTO v_MHETicketsFound
from ticketfeatures t, "mhevent_metadata2" s  
where t.troubletype  in (''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null 
and s.ticket = t.ticketnumber and t.seriousmetadata=1;

select count(*) INTO v_MHETicketsTheir
from ticketfeatures t, "mhevent_metadata2" s  
where t.troubletype  in (''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null 
and s.ticket = t.ticketnumber and  s.short_mhevent_ser1notser0=1;



--Short MHE Tickets

 select count(*) INTO v_ShortMHETicketsMatched
from ticketfeatures t, "short_mhe_ticket_fire_blow_smok" s 
 where t.troubletype  in (''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null
  and s.ticket = t.ticketnumber and t.seriousmetadata=1 and s.ser1_notser0=1;


  select count(*) INTO v_ShortMHETicketsFound
from ticketfeatures t, "short_mhe_ticket_fire_blow_smok" s 
 where t.troubletype  in (''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null
  and s.ticket = t.ticketnumber and t.seriousmetadata=1;

  select count(*) INTO v_ShortMHETicketsTheir
from ticketfeatures t, "short_mhe_ticket_fire_blow_smok" s 
 where t.troubletype  in (''MHX'', ''MHF'', ''MHO'') and seriousmetadata is not null
  and s.ticket = t.ticketnumber and  s.ser1_notser0=1;


--Calculation


--ShortSMHTickets
v_sSMHRecall := CAST(v_sSMHTicketsMatched as REAL) / CAST(v_sSMHTicketsTheir as REAL);
v_sSMHPrecision := CAST(v_sSMHTicketsMatched as REAL) / CAST(v_sSMHTicketsFound as REAL);

--MHE Tickets
v_MHERecall := CAST(v_MHETicketsMatched as REAL) / CAST(v_MHETicketsTheir as REAL);
v_MHEPrecision := CAST(v_MHETicketsMatched as REAL) / CAST(v_MHETicketsFound as REAL);


--Short MHE Tickets
v_ShortMHERecall := CAST(v_ShortMHETicketsMatched as REAL) / CAST(v_ShortMHETicketsTheir as REAL);
v_ShortMHEPrecision := CAST(v_ShortMHETicketsMatched as REAL) / CAST(v_ShortMHETicketsFound as REAL);


  --Print


  RAISE NOTICE ''=============================='';
  RAISE NOTICE ''Short SMH Tickets: recall: % precision % '',  v_sSMHRecall, v_sSMHPrecision;
   RAISE NOTICE ''MHE Tickets: recall: % precision % '',  v_MHERecall, v_MHEPrecision;
   RAISE NOTICE ''Short MHE Tickets: recall: % precision % '',  v_ShortMHERecall, v_ShortMHEPrecision;
  
END;
' LANGUAGE 'plpgsql';

-- SELECT evaluate_Referred_and_Seriousness_ticket_features2();